﻿using System;
using System.Collections.Generic;
using System.Text;
using X.DataFactory;
using System.Data;
using X.Entity;

namespace X.Data.Access
{
    public class Product : IProduct
    {
        public int Create(Entity.ProductInfo productInfo)
        {
            string sSQL = @"INSERT INTO X_Product(ProductName,CategoryId,SpecCategoryId,IsHotProduct,IsAvailable,IsRecommendatory,Title,Keyword,Description,Tag,ProductDescribe,Price,Model,PartNumber,Certificate,Color,Guarantee,HRC,Material,OrderNum)
                            VALUES(@ProductName,@CategoryId,@SpecCategoryId,@IsHotProduct,@IsAvailable,@IsRecommendatory,@Title,@Keyword,@Description,@Tag,@ProductDescribe,@Price,@Model,@PartNumber,@Certificate,@Color,@Guarantee,@HRC,@Material,@OrderNum)";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductName", DbType.String, productInfo.ProductName);
            cmdParams.AddInParameter("@CategoryId", DbType.Int32, productInfo.CategoryId);
            cmdParams.AddInParameter("@SpecCategoryId", DbType.Int32, productInfo.SpecCategoryId);
            cmdParams.AddInParameter("@IsHotProduct", DbType.Boolean, productInfo.IsHotProduct);
            cmdParams.AddInParameter("@IsAvailable", DbType.Boolean, productInfo.IsAvailable);
            cmdParams.AddInParameter("@IsRecommendatory", DbType.Boolean, productInfo.IsRecommendatory);
            cmdParams.AddInParameter("@Title", DbType.String, productInfo.Title);
            cmdParams.AddInParameter("@Keyword", DbType.String, productInfo.Keyword);
            cmdParams.AddInParameter("@Description", DbType.String, productInfo.Description);
            cmdParams.AddInParameter("@Tag", DbType.String, productInfo.Tag);
            //cmdParams.AddInParameter("@Photo", DbType.String, productInfo.Photo);
            cmdParams.AddInParameter("@ProductDescribe", DbType.String, productInfo.ProductDescribe);
            cmdParams.AddInParameter("@Price", DbType.Decimal, productInfo.Price);

            cmdParams.AddInParameter("@Model", DbType.String, productInfo.Model);
            cmdParams.AddInParameter("@PartNumber", DbType.String, productInfo.PartNumber);
            cmdParams.AddInParameter("@Certificate", DbType.String, productInfo.Certificate);
            cmdParams.AddInParameter("@Color", DbType.String, productInfo.Color);
            cmdParams.AddInParameter("@Guarantee", DbType.String, productInfo.Guarantee);
            cmdParams.AddInParameter("@HRC", DbType.String, productInfo.HRC);
            cmdParams.AddInParameter("@Material", DbType.String, productInfo.Material);
            cmdParams.AddInParameter("@OrderNum", DbType.Int32, productInfo.OrderNum);

            DBHelper.ExecuteSql(sSQL, cmdParams);

            sSQL = "UPDATE X_ProductCategory SET ProductCount = ProductCount + 1 WHERE CategoryId = @CategoryId";
            cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryId", DbType.Int32, productInfo.CategoryId);
            DBHelper.ExecuteSql(sSQL, cmdParams);

            string[] arr = productInfo.Tag.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            sSQL = "SELECT ProductId FROM X_Product WHERE ProductName = @ProductName";
            cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductName", DbType.String, productInfo.ProductName);

            int productId = Convert.ToInt32(DBHelper.ExecuteScalarSql(sSQL, cmdParams));

            if (arr.Length > 0)
            {
                sSQL = string.Empty;

                for (int i = 0; i < arr.Length; i++)
                {
                    DBHelper.ExecuteSql(string.Format("INSERT INTO X_ProductTag(ProductId,Tag,IsShow) VALUES({0},'{1}',true);", productId, arr[i]));
                }
            }

            return productId;
        }

        public bool Delete(int productId)
        {
            string sSQL = "SELECT CategoryId FROM X_Product WHERE ProductId = @ProductId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductId", DbType.String, productId);

            int categoryId = Convert.ToInt32(DBHelper.ExecuteScalarSql(sSQL, cmdParams));

            DBHelper.ExecuteNonQuerySql("DELETE FROM X_Product WHERE ProductId = @ProductId", cmdParams);
            DBHelper.ExecuteNonQuerySql("DELETE FROM X_ProductTag WHERE ProductId = @ProductId", cmdParams);
            DBHelper.ExecuteNonQuerySql("DELETE FROM X_Images WHERE ProductId = @ProductId", cmdParams);

            sSQL = "UPDATE X_ProductCategory SET ProductCount = ProductCount - 1 WHERE CategoryId = @CategoryId";
            cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryId", DbType.Int32, categoryId);

            DBHelper.ExecuteSql(sSQL, cmdParams);

            return true;
        }

        public Entity.ProductInfo GetProduct(int productId)
        {
            string sSQL = @"SELECT A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.ProductId = @ProductId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductId", DbType.Int32, productId);

            ProductInfo productInfo;

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    return ProductFromReader(reader);
                }

                productInfo = new ProductInfo(true);
            }

            return productInfo;
        }

        public Entity.ProductInfo GetProduct(string productName)
        {
            string sSQL = @"SELECT A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.ProductName = @ProductName";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductName", DbType.String, productName);

            ProductInfo productInfo;

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    return ProductFromReader(reader);
                }

                productInfo = new ProductInfo(true);
            }

            return productInfo;
        }

        public IList<Entity.ProductInfo> GetAllList()
        {
            string sSQL = @"SELECT A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            ORDER BY A.OrderNum";

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }

        public IList<Entity.ProductInfo> GetList()
        {
            string sSQL = @"SELECT A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.IsAvailable = true
                            ORDER BY A.OrderNum";

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }

        public IList<ProductInfo> GetList(int topCount)
        {
            string sSQL = @"SELECT TOP {0} A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.IsAvailable = true";

            sSQL = string.Format(sSQL, topCount);

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }

        public IList<ProductInfo> Search(string keyword)
        {
            string sSQL = @"SELECT A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.IsAvailable = true 
                            AND (A.ProductName LIKE '%{0}%' 
                                OR A.Tag LIKE '%{0}%' 
                                OR A.ProductDescribe LIKE '%{0}%' 
                                OR B.CategoryName LIKE '%{0}%' 
                                OR A.Model LIKE '%{0}%' 
                                OR A.PartNumber LIKE '%{0}%' 
                                OR A.Certificate LIKE '%{0}%' 
                                OR A.Color LIKE '%{0}%' 
                                OR A.Guarantee LIKE '%{0}%' 
                                OR A.HRC LIKE '%{0}%' 
                                OR Material LIKE '%{0}%')
                            ORDER BY A.OrderNum";

            sSQL = string.Format(sSQL, keyword);

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }

        public IList<ProductInfo> GetHotList(int topCount)
        {
            string sSQL = @"SELECT TOP {0} A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.IsAvailable = true AND A.IsHotProduct = true
                            ORDER BY A.OrderNum";

            sSQL = string.Format(sSQL, topCount);

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }
        public IList<ProductInfo> GetRecList(int topCount)
        {
            string sSQL = @"SELECT TOP {0} A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.IsAvailable = true AND A.IsRecommendatory = true
                            ORDER BY A.OrderNum";

            sSQL = string.Format(sSQL, topCount);

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }

        public IList<Entity.ProductInfo> GetListByCategoryName(string categoryName)
        {
            string sSQL = @"SELECT A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE B.CategoryName = @CategoryName AND A.IsAvailable = true
                            ORDER BY A.OrderNum";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryName", DbType.String, categoryName);

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }

        public IList<Entity.ProductInfo> GetListByCategoryId(int categoryId)
        {
            string sSQL = @"SELECT A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.CategoryId = @CategoryId AND A.IsAvailable = true
                            ORDER BY A.OrderNum";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryId", DbType.Int32, categoryId);

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }

        public IList<Entity.ProductInfo> GetListByPrice(decimal lowPrice, decimal hightPrice)
        {
            string sSQL = @"SELECT A.*,B.CategoryName
                            FROM X_Product A INNER JOIN X_ProductCategory B ON A.CategoryId = B.CategoryId
                            WHERE A.[Price] BETWEEN @LowPrice AND @HightPrice AND A.IsAvailable = true
                            ORDER BY A.OrderNum";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@LowPrice", DbType.Decimal, lowPrice);
            cmdParams.AddInParameter("@HightPrice", DbType.Decimal, hightPrice);

            IList<ProductInfo> list = new List<ProductInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    list.Add(ProductFromReader(reader));
                }
            }

            return list;
        }

        public bool Update(Entity.ProductInfo productInfo)
        {
            string sSQL = @"UPDATE X_Product 
                            SET ProductName = @ProductName
                            ,CategoryId = @CategoryId
                            ,SpecCategoryId = @SpecCategoryId
                            ,IsHotProduct = @IsHotProduct
                            ,IsAvailable = @IsAvailable
                            ,IsRecommendatory = @IsRecommendatory
                            ,Title = @Title
                            ,Keyword = @Keyword
                            ,Description = @Description
                            ,Tag = @Tag
                            ,ProductDescribe = @ProductDescribe
                            ,Price = @Price
                            ,Model = @Model
                            ,PartNumber = @PartNumber
                            ,Certificate = @Certificate
                            ,Color = @Color
                            ,Guarantee = @Guarantee
                            ,HRC = @HRC
                            ,Material = @Material
                            ,OrderNum = @OrderNum
                            WHERE ProductId = @ProductId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductName", DbType.String, productInfo.ProductName);
            cmdParams.AddInParameter("@CategoryId", DbType.Int32, productInfo.CategoryId);
            cmdParams.AddInParameter("@SpecCategoryId", DbType.Int32, productInfo.SpecCategoryId);
            cmdParams.AddInParameter("@IsHotProduct", DbType.Boolean, productInfo.IsHotProduct);
            cmdParams.AddInParameter("@IsAvailable", DbType.Boolean, productInfo.IsAvailable);
            cmdParams.AddInParameter("@IsRecommendatory", DbType.Boolean, productInfo.IsRecommendatory);
            cmdParams.AddInParameter("@Title", DbType.String, productInfo.Title);
            cmdParams.AddInParameter("@Keyword", DbType.String, productInfo.Keyword);
            cmdParams.AddInParameter("@Description", DbType.String, productInfo.Description);
            cmdParams.AddInParameter("@Tag", DbType.String, productInfo.Tag);
            //cmdParams.AddInParameter("@Photo", DbType.String, productInfo.Photo);
            cmdParams.AddInParameter("@ProductDescribe", DbType.String, productInfo.ProductDescribe);
            cmdParams.AddInParameter("@Price", DbType.Decimal, productInfo.Price);

            cmdParams.AddInParameter("@Model", DbType.String, productInfo.Model);
            cmdParams.AddInParameter("@PartNumber", DbType.String, productInfo.PartNumber);
            cmdParams.AddInParameter("@Certificate", DbType.String, productInfo.Certificate);
            cmdParams.AddInParameter("@Color", DbType.String, productInfo.Color);
            cmdParams.AddInParameter("@Guarantee", DbType.String, productInfo.Guarantee);
            cmdParams.AddInParameter("@HRC", DbType.String, productInfo.HRC);
            cmdParams.AddInParameter("@Material", DbType.String, productInfo.Material);
            cmdParams.AddInParameter("@OrderNum", DbType.Int32, productInfo.OrderNum);

            cmdParams.AddInParameter("@ProductId", DbType.Int32, productInfo.ProductId);

            bool rtnVal = DBHelper.ExecuteSql(sSQL, cmdParams);

            string[] arr = productInfo.Tag.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            if (arr.Length > 0)
            {
                DBHelper.ExecuteNonQuerySql(string.Format("DELETE FROM X_ProductTag WHERE ProductId = {0};", productInfo.ProductId));

                for (int i = 0; i < arr.Length; i++)
                {
                    DBHelper.ExecuteNonQuerySql(string.Format("INSERT INTO X_ProductTag(ProductId,Tag) VALUES({0},'{1}');", productInfo.ProductId, arr[i]));
                }
            }

            return rtnVal;
        }

        private ProductInfo ProductFromReader(NullableDataReader reader)
        {
            ProductInfo productInfo = new ProductInfo();

            productInfo.ProductId = reader.GetInt32("ProductId");
            productInfo.ProductName = reader.GetString("ProductName");
            productInfo.CategoryId = reader.GetInt32("CategoryId");
            productInfo.SpecCategoryId = reader.GetInt32("SpecCategoryId");
            productInfo.IsHotProduct = reader.GetBoolean("IsHotProduct");
            productInfo.IsAvailable = reader.GetBoolean("IsAvailable");
            productInfo.IsRecommendatory = reader.GetBoolean("IsRecommendatory");
            productInfo.Title = reader.GetString("Title");
            productInfo.Keyword = reader.GetString("Keyword");
            productInfo.Description = reader.GetString("Description");
            productInfo.Tag = reader.GetString("Tag");
            //productInfo.Photo = reader.GetString("Photo");
            productInfo.ProductDescribe = reader.GetString("ProductDescribe");
            productInfo.Price = reader.GetDecimal("Price");

            productInfo.CategoryName = reader.GetString("CategoryName");
            //productInfo.SpecCategoryName = reader.GetString("SpecCategoryName");

            productInfo.Model = reader.GetString("Model");
            productInfo.PartNumber = reader.GetString("PartNumber");
            productInfo.Certificate = reader.GetString("Certificate");
            productInfo.Color = reader.GetString("Color");
            productInfo.Guarantee = reader.GetString("Guarantee");
            productInfo.HRC = reader.GetString("HRC");
            productInfo.Material = reader.GetString("Material");
            productInfo.OrderNum = reader.GetInt32("OrderNum");

            string sSQL = "SELECT * FROM X_Images WHERE ProductId = @ProductId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductId", DbType.Int32, productInfo.ProductId);

            using (NullableDataReader r = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                IList<ProductInfo.ImageInfo> list = new List<ProductInfo.ImageInfo>();

                while (r.Read())
                {
                    ProductInfo.ImageInfo image = new ProductInfo.ImageInfo();

                    image.Id = r.GetInt32("Id");
                    image.ProductId = r.GetInt32("ProductId");
                    image.ImageName = r.GetString("ImageName");
                    image.ImageNameEx = image.ImageName.Replace(".", "_s.");    //TODO:

                    list.Add(image);
                }

                if (0 == list.Count)
                {
                    ProductInfo.ImageInfo image = new ProductInfo.ImageInfo();
                    image.Id = 0;
                    image.ProductId = 0;
                    image.ImageName = "";
                    image.ImageNameEx = "";

                    list.Add(image);
                }

                productInfo.Photo = list;
            }

            return productInfo;
        }

        public void UploadImage(int productId, IList<string> list)
        {
            string sSQL = @"INSERT INTO X_Images(ProductId,ImageName) 
                            VALUES(@ProductId,@ImageName)";

            for (int i = 0; i < list.Count; i++)
            {
                Parameters cmdParams = new Parameters();
                cmdParams.AddInParameter("@ProductId", DbType.Int32, productId);
                cmdParams.AddInParameter("@ImageName", DbType.String, list[i]);

                DBHelper.ExecuteNonQuerySql(sSQL, cmdParams);
            }
        }

        public void DeleteImage(int imageId)
        {
            string sSQL = "DELETE FROM X_Images WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@Id", DbType.Int32, imageId);

            DBHelper.ExecuteNonQuerySql(sSQL, cmdParams);
        }

        public IList<string> GetImagesList(int productId)
        {
            string sSQL = "SELECT ImageName FROM X_Images WHERE ProductId = @ProductId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductId", DbType.Int32, productId);

            IList<string> list = new List<string>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetString("ImageName"));
                }
            }

            return list;
        }

        public IList<PriceInfo> GetPriceList()
        {
            string sSQL = "SELECT * FROM X_PriceList ORDER BY LowPrice";
            IList<PriceInfo> list = new List<PriceInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    PriceInfo priceInfo = new PriceInfo();

                    priceInfo.Id = reader.GetInt32("Id");
                    priceInfo.LowPrice = reader.GetInt32("LowPrice");
                    priceInfo.HightPrice = reader.GetInt32("HightPrice");

                    list.Add(priceInfo);
                }
            }

            return list;
        }

        public bool CreatePrice(PriceInfo priceInfo)
        {
            string sSQL = @"INSERT INTO X_PriceList(LowPrice,HightPrice)
                            VALUES(@LowPrice,@HightPrice)";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@LowPrice", DbType.Int32, priceInfo.LowPrice);
            cmdParams.AddInParameter("@HightPrice", DbType.Int32, priceInfo.HightPrice);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public bool UpdatePrice(PriceInfo priceInfo)
        {
            string sSQL = @"UPDATE X_PriceList 
                            SET LowPrice = @LowPrice,HightPrice = @HightPrice
                            WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@LowPrice", DbType.Int32, priceInfo.LowPrice);
            cmdParams.AddInParameter("@HightPrice", DbType.Int32, priceInfo.HightPrice);
            cmdParams.AddInParameter("@Id", DbType.Int32, priceInfo.Id);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public bool DeletePrice(int id)
        {
            string sSQL = @"DELETE FROM X_PriceList WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@Id", DbType.Int32, id);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public PriceInfo GetPriceInfo(int id)
        {
            string sSQL = "SELECT * FROM X_PriceList";

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    PriceInfo priceInfo = new PriceInfo();

                    priceInfo.Id = reader.GetInt32("Id");
                    priceInfo.LowPrice = reader.GetInt32("LowPrice");
                    priceInfo.HightPrice = reader.GetInt32("HightPrice");

                    return priceInfo;
                }
            }

            return new PriceInfo(true);
        }

        public IList<TagInfo> GetTagList()
        {
            string sSQL = @"SELECT A.*
                            FROM [X_ProductTag] A INNER JOIN [X_Product] B ON A.ProductId = B.ProductId
                            WHERE B.IsAvailable = true AND A.IsShow = true
                            ORDER BY A.OrderNum";

            IList<TagInfo> list = new List<TagInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    TagInfo tagInfo = TagFromReader(reader);

                    if (!list.Contains(tagInfo))
                        list.Add(tagInfo);
                }
            }

            return list;
        }

        public IList<TagInfo> GetAllTagList()
        {
            string sSQL = @"SELECT A.*
                            FROM X_ProductTag A INNER JOIN X_Product B ON A.ProductId = B.ProductId
                            WHERE B.IsAvailable = true";

            IList<TagInfo> list = new List<TagInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(TagFromReader(reader));
                }
            }

            return list;
        }

        public TagInfo GetTagInfo(int tagId)
        {
            string sSQL = @"SELECT A.*
                            FROM X_ProductTag A INNER JOIN X_Product B ON A.ProductId = B.ProductId
                            WHERE A.TagId = @TagId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@TagId", DbType.Int32, tagId);

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    return TagFromReader(reader);
                }
            }

            return new TagInfo(true);
        }

        public bool UpdateTag(TagInfo tagInfo)
        {
            string sSQL = @"UPDATE X_ProductTag
                            SET Tag = @Tag,IsShow = @IsShow,IsBold = @IsBold,OrderNum = @OrderNum
                            WHERE TagId = @TagId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@Tag", DbType.String, tagInfo.Tag);
            cmdParams.AddInParameter("@IsShow", DbType.Boolean, tagInfo.IsShow);
            cmdParams.AddInParameter("@IsBold", DbType.Boolean, tagInfo.IsBold);
            cmdParams.AddInParameter("@OrderNum", DbType.Int32, tagInfo.OrderNum);
            cmdParams.AddInParameter("@TagId", DbType.Int32, tagInfo.TagId);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        private TagInfo TagFromReader(NullableDataReader reader)
        {
            TagInfo tagInfo = new TagInfo();

            tagInfo.TagId = reader.GetInt32("TagId");
            tagInfo.Tag = reader.GetString("Tag");
            tagInfo.IsShow = reader.GetBoolean("IsShow");
            tagInfo.IsBold = reader.GetBoolean("IsBold");
            tagInfo.OrderNum = reader.GetInt32("OrderNum");
            tagInfo.ProductId = reader.GetInt32("ProductId");

            return tagInfo;
        }
    }
}
